package com.util;

import com.bean.Farmer;
import com.bean.Users;

import java.sql.*;
import java.util.*;

public class SelectUsers {
    private String id = "sa";
    private String pwd = "123456";
    private String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=FarmProductSalesSystem";
    private List<Users> userList;

    public List<Users> getUserData(){
        userList = new ArrayList<>();
        try {
            //加载驱动
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        //获取连接
        Connection conn = null;
        Statement stat = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection(url,id,pwd);
            String sql = "select * from Users";
            stat = conn.createStatement(); //用于执行sql语句
            rs = stat.executeQuery(sql); //返回查询后的结果
            while(rs.next()){
                String UserID = rs.getString("UserID");
                String UserType = rs.getString("UserType");
                String Username = rs.getString("Username");
                String Password = rs.getString("Password");
                String Gender = rs.getString("Gender");
                String PhoneNumber = rs.getString("PhoneNumber");
                String Email = rs.getString("Email");
                String Address = rs.getString("Address");
                Users user = new Users(Integer.parseInt(UserID),UserType,Username,Password,Gender,PhoneNumber,Email,Address);
                userList.add(user);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("连接数据库失败！");
        }finally{
            //关闭coon
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            //关闭stat
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            //关闭rs
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return userList;
    }


    public int getUserID(){
        List<Users> list = getUserData();
        int ID = 0;
        for(Users u:list)
        {
            ID = u.getUserId();
        }
        return ID;
    }

    public int getUserID(String userName){
        List<Users> list = getUserData();
        int ID = 0;
        for(Users u:list)
        {
            if(u.getUserName().equals(userName)){
                ID = u.getUserId();
                break;
            }
        }
        return ID;
    }

}